set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;


insert overwrite table jms_dm.dm_slowest_route_area_effective
select
     slowest.update_date
    ,slowest.is_main_route
    ,slowest.in_from_regional_code
    ,slowest.in_from_regional_desc
    ,slowest.in_from_financial_center_code
    ,slowest.in_from_financial_center_desc
    ,slowest.in_from_provider_code
    ,slowest.in_from_provider_desc
    ,slowest.in_from_city_code
    ,slowest.in_from_city_desc
    ,slowest.in_from_area_code
    ,slowest.in_from_area_desc
    ,slowest.in_collect_code
    ,slowest.in_collect_name
    ,slowest.in_from_code
    ,slowest.in_from_name
    ,slowest.in_to_code
    ,slowest.in_to_name
    ,slowest.end_center_code
    ,slowest.end_center
    ,slowest.start_center_code
    ,slowest.start_center
    ,slowest.out_to_code
    ,slowest.out_to_name
    ,slowest.out_to_regional_code
    ,slowest.out_to_regional_desc
    ,slowest.out_to_financial_center_code
    ,slowest.out_to_financial_center_desc
    ,slowest.out_to_provider_code
    ,slowest.out_to_provider_desc
    ,slowest.out_from_code
    ,slowest.out_from_name
    ,slowest.out_to_city_code
    ,slowest.out_to_city_desc
    ,slowest.out_to_area_code
    ,slowest.out_to_area_desc
    ,slowest.out_collect_code
    ,slowest.out_collect_name
    ,slowest.total_days_use
    ,slowest.total_time_use
    ,slowest.total_days_t
    ,slowest.whole_route
    ,slowest.all_line_name
    ,slowest.is_main_city
    ,cn_effe.platform_code   as platform_code_cn
    ,cn_effe.platform        as platform_cn
    ,cn_effe.times           as effective_times_cn
    ,zt_effe.platform_code   as platform_code_zt
    ,zt_effe.platform        as platform_zt
    ,zt_effe.times           as effective_times_zt
    ,sf_effe.platform_code  as platform_code_sf
    ,sf_effe.platform       as platform_sf
    ,sf_effe.times          as effective_times_sf
    ,yt_effe.platform_code  as platform_code_yt
    ,yt_effe.platform       as platform_yt
    ,yt_effe.times          as effective_times_yt
    ,yd_effe.platform_code  as platform_code_yd
    ,yd_effe.platform       as platform_yd
    ,yd_effe.times          as effective_times_yd
    ,thd_effe.platform_code as platform_code_thd
    ,thd_effe.platform      as platform_thd
    ,thd_effe.times         as effective_times_thd
    ,zjs_effe.platform_code as platform_code_zjs
    ,zjs_effe.platform      as platform_zjs
    ,zjs_effe.times         as effective_times_zjs
    ,slowest.dt as dt
from (
    select
         update_date
        ,is_main_route
        ,in_from_regional_code
        ,in_from_regional_desc
        ,in_from_financial_center_code
        ,in_from_financial_center_desc
        ,in_from_provider_code
        ,in_from_provider_desc
        ,in_from_city_code
        ,in_from_city_desc
        ,in_from_area_code
        ,in_from_area_desc
        ,in_collect_code
        ,in_collect_name
        ,in_from_code
        ,in_from_name
        ,in_to_code
        ,in_to_name
        ,end_center_code
        ,end_center
        ,start_center_code
        ,start_center
        ,out_to_code
        ,out_to_name
        ,out_to_regional_code
        ,out_to_regional_desc
        ,out_to_financial_center_code
        ,out_to_financial_center_desc
        ,out_to_provider_code
        ,out_to_provider_desc
        ,out_from_code
        ,out_from_name
        ,out_to_city_code
        ,out_to_city_desc
        ,out_to_area_code
        ,out_to_area_desc
        ,out_collect_code
        ,out_collect_name
        ,total_days_use
        ,total_time_use
        ,total_days_t
        ,whole_route
        ,all_line_name
        ,is_main_city
        ,dt
     from jms_dm.dm_route_city_slowest_dt --路由区县时效最慢表
    where dt='{{ execution_date | cst_ds }}'
) slowest
left join(
    select *
     from jms_dim.dim_tab_rou_competitor_effective --同行时效配置表
     where platform_code = 1 --菜鸟
) cn_effe on cn_effe.sender_city_id = slowest.in_from_city_code
         and cn_effe.receiver_city_id = slowest.out_to_city_code
left join(
    select *
    from jms_dim.dim_tab_rou_competitor_effective
    where platform_code = 2 --中通
) zt_effe on zt_effe.sender_city_id = slowest.in_from_city_code
         and zt_effe.receiver_city_id = slowest.out_to_city_code
left join(
    select *
    from jms_dim.dim_tab_rou_competitor_effective
    where platform_code = 3 --顺丰
) sf_effe on sf_effe.sender_city_id = slowest.in_from_city_code
         and sf_effe.receiver_city_id = slowest.out_to_city_code
left join(
    select *
    from jms_dim.dim_tab_rou_competitor_effective
    where platform_code = 4 --圆通
) yt_effe on yt_effe.sender_city_id = slowest.in_from_city_code
         and yt_effe.receiver_city_id = slowest.out_to_city_code
left join(
    select *
    from jms_dim.dim_tab_rou_competitor_effective
    where platform_code = 5 --韵达
) yd_effe on yd_effe.sender_city_id = slowest.in_from_city_code
         and yd_effe.receiver_city_id = slowest.out_to_city_code
left join(
    select *
    from jms_dim.dim_tab_rou_competitor_effective
    where platform_code = 6 --桃花岛
) thd_effe on thd_effe.sender_city_id = slowest.in_from_city_code
          and thd_effe.receiver_city_id = slowest.out_to_city_code
left join(
    select *
    from jms_dim.dim_tab_rou_competitor_effective
    where platform_code = 7 --紫金山
) zjs_effe on zjs_effe.sender_city_id = slowest.in_from_city_code
          and zjs_effe.receiver_city_id = slowest.out_to_city_code
distribute by dt,pmod(hash(rand()),15);


